﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DSPL.Contracts;
using DSPL.Contracts.Common;
using DSPL.Contracts.Interfaces;
using DSPL.DataAccess;

namespace DSPL.Services.Services
{
    public class TransactionWriteService : ITransactionWriteService
    {
        DatabaseAccess db = DatabaseAccess.Instance ;
        public int PrimaryKey { get; set; }

        public string ReferenceNo { get; set; }

        #region IMasterWriteService Members

        

        #endregion

        #region private Methods

        private int GetPrimaryKey(string tableName, ref  SqlTransaction sqlTran)
        {
            List<ParameterWithValue> paramList = new List<ParameterWithValue>();
            paramList.Add(new ParameterWithValue("@tableName", tableName));
            paramList.Add(new ParameterWithValue("@nextPk", 0, SqlDbType.Int, ParameterDirection.InputOutput));

            SpCaller spCaller = new SpCaller() { SpName = "SP_GET_PRIMARYKEY", SpParamValues = paramList.ToArray() };
            return (int)db.Excecute(spCaller, "@nextPk", ref sqlTran);
        }

        private object GetDefaultVlue(string DataType)
        {
            object returnValue = null;

            switch (DataType)
            {
                case "STRING":
                    returnValue = string.Empty;
                    break;

                case "DATETIME":
                    returnValue = new DateTime(1900, 01, 01); ;
                    break;

                case "BOOLEAN":
                    returnValue = false;
                    break;

                case "INT16":
                    returnValue = 0;
                    break;

                case "UINT16":
                    returnValue = 0;
                    break;

                case "INT32":
                    returnValue = 0;
                    break;

                case "UINT32":
                    returnValue = 0;
                    break;

                case "INT64":
                    returnValue = 0L;
                    break;

                case "UINT64":
                    returnValue = 0;
                    break;

                case "LONG":
                    returnValue = 0L;
                    break;

                case "DECIMAL":
                    returnValue = 0.0M;
                    break;

                case "DOUBLE":
                    returnValue = 0.0D;
                    break;

                case "BYTE":
                    returnValue = 0;
                    break;

                case "SBYTE":
                    returnValue = 0;
                    break;

                 case "CHAR":
                    returnValue = "\0";
                    break;

                 case "SINGLE":
                    returnValue = 0.0F;
                    break;

                default:
                    break;
            }

            return returnValue;
        }

        #endregion

        public bool UpdatePurchaseOrderSpecificData(DataRow purchaseData, DataTable dtTaxData, ref SqlTransaction sqlTran, Modes formMode = Modes.Edit)
        {
            List<ColumnWithValue> insertFieldList = new List<ColumnWithValue>();

            var purchaseService = DatabaseAccess.Instance;
            int intKey = Convert.ToInt32(purchaseData["PoPk"]);

            if (!DeletePurchaseOrderSpecificData(intKey, ref sqlTran, formMode))
            {
                return false;
            }

            DateTime txnDate = DateTime.Now;

            foreach (DataRow drTaxData in dtTaxData.Rows)
            {
                insertFieldList.Clear();
                if ((drTaxData["TaxComPk"]) == null)
                {
                    continue;
                }
                insertFieldList.Add(new ColumnWithValue { ColumnName = "ItemPk", ColumnValue = drTaxData["ItemPk"] });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "TaxComPk", ColumnValue = drTaxData["TaxComPk"] });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "TaxPk", ColumnValue = drTaxData["TaxPk"] });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "TaxPer", ColumnValue = drTaxData["TaxPer"] });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "TaxRate", ColumnValue = drTaxData["TaxRate"] });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "TaxSeq", ColumnValue = drTaxData["TaxSeq"] });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "TaxDetAmt", ColumnValue = drTaxData["TaxDetAmt"] });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "BTaxDetAmt", ColumnValue = drTaxData["BTaxDetAmt"] });
                
                var insertQuery = new Query { TableName = "fPoTaxDt", QType = QueryType.INSERT, ColValues = insertFieldList.ToArray() };
                if (purchaseService.Excecute(insertQuery, ref sqlTran) == null)
                {
                    return false;
                }
            }

            return true;

        }

        public bool DeletePurchaseOrderSpecificData(int primaryKeyValue, ref SqlTransaction sqlTran, Modes formMode = Modes.Delete)
        {
            var taxService = DatabaseAccess.Instance;

            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            List<ColumnWithValue> insertFieldList = new List<ColumnWithValue>();

            whereList.Add(new ColumnWithValue { ColumnName = "RefPk", Comparison = QComparison.EQUALS, ColumnValue = primaryKeyValue });
            var getTaxQuery = new Query() { TableName = "fPoTaxdt", QType = QueryType.SELECTALL, WhereClause = whereList.ToArray() };
            var data = taxService.ExcecuteSelect(getTaxQuery, ref sqlTran);

            DateTime delDate = DateTime.Now;


            foreach (DataRow drPoTax in data.Rows)
            {
                insertFieldList.Clear();

                foreach (DataColumn item in data.Columns)
                {
                    if (!GlobalVariables.MasterLogIgnoreList.Contains(item.ColumnName.ToUpper()))
                    {
                        insertFieldList.Add(new ColumnWithValue() { ColumnName = item.ColumnName, ColumnValue = drPoTax[item.ColumnName] });
                    }
                }

                insertFieldList.Add(new ColumnWithValue { ColumnName = "DelDate", ColumnValue = delDate });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "DelType", ColumnValue = (formMode == Modes.Delete ? "D" : "E") });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "DelMach", ColumnValue = Environment.MachineName + "#" + Environment.UserName });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "DelUser", ColumnValue = GlobalVariables.LoggedInUser });
                var insertQuery = new Query { TableName = "lPoTaxDt", QType = QueryType.INSERT, ColValues = insertFieldList.ToArray() };
                if (taxService.Excecute(insertQuery, ref sqlTran) == null)
                {
                    return false;
                }
            }

            // deletes the selected records from the child table
            var deleteTaxQuery = new Query() { TableName = "fPoTaxdt", QType = QueryType.DELETE, WhereClause = whereList.ToArray() };
            if (taxService.Excecute(deleteTaxQuery, ref sqlTran) == null)
            {
                return false;
            }

            return true;
        }

        public bool UpdateGoodReceiveSpecificData(DataRow grnData, DataTable dtCcrNote, DataTable dtImExp,DataTable dtGrnTax,ref SqlTransaction sqlTran, Modes formMode = Modes.Edit)
        {
            List<ColumnWithValue> insertFieldList = new List<ColumnWithValue>();

            var grnService = DatabaseAccess.Instance;
            int intKey = Convert.ToInt32(grnData["GrnPk"]);
            string refNo2 = (grnData["RefNo2"]==DBNull.Value)? "" :Convert.ToString(grnData["RefNo2"]);

            if (!DeleteGoodReceiveSpecificData(intKey, refNo2, ref sqlTran, formMode))
            {
                return false;
            }

            if (formMode == Modes.Approve)
            {
                UpdatePurchaseOrder(intKey,formMode,ref sqlTran);
            }

            DateTime txnDate = DateTime.Now;

            string ignoreFields = "AddUser,AddDate,AddMach,CCrPk,RefNo";

            // Update GRN credit notes details
            if (formMode == Modes.Approve)
            {
                foreach (DataRow drCcrNote in dtCcrNote.Rows)
                {
                    insertFieldList.Clear();
                    foreach (DataColumn item in dtCcrNote.Columns)
                    {
                        if (!ignoreFields.Contains(item.ColumnName.ToUpper()))
                        {
                            insertFieldList.Add(new ColumnWithValue() { ColumnName = item.ColumnName, ColumnValue = drCcrNote[item.ColumnName] });
                        }
                    }

                    insertFieldList.Add(new ColumnWithValue() { ColumnName = "CcrPk", ColumnValue = intKey });
                    insertFieldList.Add(new ColumnWithValue() { ColumnName = "RefNo", ColumnValue = grnData["RefNo"].ToString() });
                    insertFieldList.Add(new ColumnWithValue() { ColumnName = "AddUser", ColumnValue = GlobalVariables.LoggedInUser });
                    insertFieldList.Add(new ColumnWithValue() { ColumnName = "AddDate", ColumnValue = DateTime.Now });
                    insertFieldList.Add(new ColumnWithValue() { ColumnName = "AddMach", ColumnValue = GlobalVariables.MachineName }); ;

                    var insertQuery = new Query { TableName = "fCcrNote", QType = QueryType.INSERT, ColValues = insertFieldList.ToArray() };
                    if (grnService.Excecute(insertQuery, ref sqlTran) == null)
                    {
                        return false;
                    }
                }

                // Update Grn Import Expenses

                foreach (DataRow drImExp in dtImExp.Rows)
                {
                    insertFieldList.Clear();
                    foreach (DataColumn item in dtImExp.Columns)
                    {
                        if (!ignoreFields.Contains(item.ColumnName.ToUpper()))
                        {
                            insertFieldList.Add(new ColumnWithValue() { ColumnName = item.ColumnName, ColumnValue = drImExp[item.ColumnName] });
                        }
                    }

                    var insertQuery = new Query { TableName = "fGrnImExp", QType = QueryType.INSERT, ColValues = insertFieldList.ToArray() };
                    if (grnService.Excecute(insertQuery, ref sqlTran) == null)
                    {
                        return false;
                    }
                }
            }

            // Update Tax Details

            foreach (DataRow drGrnTax in dtGrnTax.Rows)
            {
                insertFieldList.Clear();
                foreach (DataColumn item in dtGrnTax.Columns)
                {
                    if (!ignoreFields.Contains(item.ColumnName.ToUpper()))
                    {
                        insertFieldList.Add(new ColumnWithValue() { ColumnName = item.ColumnName, ColumnValue = drGrnTax[item.ColumnName] });
                    }
                }

                var insertQuery = new Query { TableName = "fGrnTaxDt", QType = QueryType.INSERT, ColValues = insertFieldList.ToArray() };
                if (grnService.Excecute(insertQuery, ref sqlTran) == null)
                {
                    return false;
                }
            }
            
            return true;
        }

        public bool DeleteGoodReceiveSpecificData(int primaryKeyValue, string refNo2, ref SqlTransaction sqlTran, Modes formMode = Modes.Delete)
        {
            var grnService = DatabaseAccess.Instance;

            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            List<ColumnWithValue> insertFieldList = new List<ColumnWithValue>();

            whereList.Add(new ColumnWithValue { ColumnName = "CcrPk", Comparison = QComparison.EQUALS, ColumnValue = primaryKeyValue });
            var getCcrNoteQuery = new Query() { TableName = "fCcrNote", QType = QueryType.SELECTALL, WhereClause = whereList.ToArray() };
            var data = grnService.ExcecuteSelect(getCcrNoteQuery, ref sqlTran);

            DateTime delDate = DateTime.Now;

            foreach (DataRow drCcrNote in data.Rows)
            {
                insertFieldList.Clear();

                foreach (DataColumn item in data.Columns)
                {
                    if (!GlobalVariables.MasterLogIgnoreList.Contains(item.ColumnName.ToUpper()))
                    {
                        insertFieldList.Add(new ColumnWithValue() { ColumnName = item.ColumnName, ColumnValue = drCcrNote[item.ColumnName] });
                    }
                }

                insertFieldList.Add(new ColumnWithValue { ColumnName = "DelDate", ColumnValue = delDate });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "DelType", ColumnValue = (formMode == Modes.Delete ? "D" : "E") });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "DelMach", ColumnValue = Environment.MachineName + "#" + Environment.UserName });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "DelUser", ColumnValue = GlobalVariables.LoggedInUser });
                var insertQuery = new Query { TableName = "lCcrNote", QType = QueryType.INSERT, ColValues = insertFieldList.ToArray() };
                if (grnService.Excecute(insertQuery, ref sqlTran) == null)
                {
                    return false;
                }
            }

            // deletes the selected records from the child table
            var deleteCcrQuery = new Query() { TableName = "fCcrNote", QType = QueryType.DELETE, WhereClause = whereList.ToArray() };
            if (grnService.Excecute(deleteCcrQuery, ref sqlTran) == null)
            {
                return false;
            }
            
            // deletes Grn Tax Details 
            List<ColumnWithValue> taxWhereList = new List<ColumnWithValue>();
            taxWhereList.Add(new ColumnWithValue { ColumnName = "GrnPk", Comparison = QComparison.EQUALS, ColumnValue = primaryKeyValue });
            var getTaxQuery = new Query() { TableName = "fGrnTaxDt", QType = QueryType.SELECTALL, WhereClause = taxWhereList.ToArray() };
            var dtTax = grnService.ExcecuteSelect(getTaxQuery, ref sqlTran);

            foreach (DataRow drTax in dtTax.Rows)
            {
                insertFieldList.Clear();

                foreach (DataColumn item in dtTax.Columns)
                {
                    if (!GlobalVariables.MasterLogIgnoreList.Contains(item.ColumnName.ToUpper()))
                    {
                        insertFieldList.Add(new ColumnWithValue() { ColumnName = item.ColumnName, ColumnValue = drTax[item.ColumnName] });
                    }
                }

                insertFieldList.Add(new ColumnWithValue { ColumnName = "DelDate", ColumnValue = delDate });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "DelType", ColumnValue = (formMode == Modes.Delete ? "D" : "E") });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "DelMach", ColumnValue = Environment.MachineName + "#" + Environment.UserName });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "DelUser", ColumnValue = GlobalVariables.LoggedInUser });
                var insertQuery = new Query { TableName = "lGrnTaxDt", QType = QueryType.INSERT, ColValues = insertFieldList.ToArray() };
                if (grnService.Excecute(insertQuery, ref sqlTran) == null)
                {
                    return false;
                }
            }

            // deletes the selected records from the child table
            var deleteGrnTaxQuery = new Query() { TableName = "fGrnTaxDt", QType = QueryType.DELETE, WhereClause = taxWhereList.ToArray() };
            if (grnService.Excecute(deleteGrnTaxQuery, ref sqlTran) == null)
            {
                return false;
            }
            
            // deletes Grn Import Expenses
            List<ColumnWithValue> importExpWhereList = new List<ColumnWithValue>();
            importExpWhereList.Add(new ColumnWithValue { ColumnName = "GrnPk", Comparison = QComparison.EQUALS, ColumnValue = primaryKeyValue });
            var getimportExpQuery = new Query() { TableName = "fGrnTImExp", QType = QueryType.SELECTALL, WhereClause = importExpWhereList.ToArray() };
            var dtImportExp = grnService.ExcecuteSelect(getimportExpQuery, ref sqlTran);

            foreach (DataRow drImportExp in dtImportExp.Rows)
            {
                insertFieldList.Clear();

                foreach (DataColumn item in dtImportExp.Columns)
                {
                    if (!GlobalVariables.MasterLogIgnoreList.Contains(item.ColumnName.ToUpper()))
                    {
                        insertFieldList.Add(new ColumnWithValue() { ColumnName = item.ColumnName, ColumnValue = drImportExp[item.ColumnName] });
                    }
                }

                insertFieldList.Add(new ColumnWithValue { ColumnName = "DelDate", ColumnValue = delDate });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "DelType", ColumnValue = (formMode == Modes.Delete ? "D" : "E") });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "DelMach", ColumnValue = Environment.MachineName + "#" + Environment.UserName });
                insertFieldList.Add(new ColumnWithValue { ColumnName = "DelUser", ColumnValue = GlobalVariables.LoggedInUser });
                var insertQuery = new Query { TableName = "lGrnTImExp", QType = QueryType.INSERT, ColValues = insertFieldList.ToArray() };
                if (grnService.Excecute(insertQuery, ref sqlTran) == null)
                {
                    return false;
                }
            }

            // deletes the selected records from the child table
            var deleteImportQuery = new Query() { TableName = "fGrnTImExp", QType = QueryType.DELETE, WhereClause = importExpWhereList.ToArray() };
            if (grnService.Excecute(deleteImportQuery, ref sqlTran) == null)
            {
                return false;
            }


            if (formMode == Modes.Approve)
            {
                var grnTDetWhereList = new List<ColumnWithValue>();
                grnTDetWhereList.Add(new ColumnWithValue { ColumnName="GrnPk", ColumnValue=refNo2});
                var grnTDetQuery = new Query { TableName = "fGrntDet", QType = QueryType.DELETE, WhereClause = grnTDetWhereList.ToArray() };

                if (grnService.Excecute(grnTDetQuery,ref sqlTran)==null)
                {
                    return false;
                }
                                
                var grnTHedQuery = new Query { TableName = "fGrntHed", QType = QueryType.DELETE, WhereClause = grnTDetWhereList.ToArray() };

                if (grnService.Excecute(grnTHedQuery, ref sqlTran) == null)
                {
                    return false;
                }


            }
            return true;
        }

        public bool UpdatePurchaseOrder(int primaryKey,Modes  formMode, ref SqlTransaction sqlTran)
        {
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() {ColumnName="Qty" });
            columnList.Add(new ColumnWithValue() { ColumnName = "ItemPk" });
            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
           
            whereList.Add(new ColumnWithValue() {ColumnName="Linkrec", Comparison=QComparison.EQUALS, ColumnValue=1,ColumnConnector= QLogicOperator.AND});
            whereList.Add(new ColumnWithValue() { ColumnName = "GrnPk", Comparison = QComparison.EQUALS, ColumnValue = primaryKey });
            var grnDetQuery = new Query {TableName="fGrnTDet", QType=QueryType.SELECT, ColValues=columnList.ToArray(),WhereClause= whereList.ToArray() };

            var grnService = DatabaseAccess.Instance;
            DataTable dtGrnDet = grnService.ExcecuteSelect(grnDetQuery, ref sqlTran);

            string sqlQuery;
            if (formMode == Modes.Approve)
            {
                sqlQuery = "UPDATE fPodet SET BalQty = BalQty + @quantity  WHERE ItemPk=@itemPk and PoPk=@refNo";
            }
            else
            {
                sqlQuery = "UPDATE fPodet SET BalQty = BalQty - @quantity  WHERE ItemPk=@itemPk and PoPk=@refNo";
            }
            foreach (DataRow drGrnDet in dtGrnDet.Rows)
            {
                double quantity = Convert.ToDouble(drGrnDet["Qty"]);
                int itemPk = Convert.ToInt32(drGrnDet["ItemPk"]);
                
                var paramList = new List<Parameter>();
                paramList.Add(new Parameter { ParamName = "@quantity", ParamValue = quantity});
                paramList.Add(new Parameter { ParamName = "@itemPk", ParamValue = itemPk});
                paramList.Add(new Parameter { ParamName = "@refNo", ParamValue = primaryKey});
                var purchaseDetQuery = new Query { QueryString = sqlQuery, QType=QueryType.OTHER, ParameterList=paramList.ToArray() };
                if (grnService.Excecute(purchaseDetQuery,  ref sqlTran,true) == null)
                {
                    return false;
                }
            }
            return true;
           
        }


        public bool UpdateGoodReceiveReturnSpecificData(DataRow currentRow, DataTable dtChildDet, ref SqlTransaction sqlTran)
        {
            var insertFieldList = new List<ColumnWithValue>();
            
            insertFieldList.Add(new ColumnWithValue { ColumnName = "RefNo", ColumnValue = currentRow["RefNo"] });
            insertFieldList.Add(new ColumnWithValue { ColumnName = "SaleRefNo", ColumnValue =currentRow["RefNo"] });
            insertFieldList.Add(new ColumnWithValue { ColumnName = "AloRef", ColumnValue = "" });
            insertFieldList.Add(new ColumnWithValue { ColumnName = "ManuRef", ColumnValue = Convert.ToString(currentRow["ManuRef"]) });
            insertFieldList.Add(new ColumnWithValue { ColumnName = "TxnType", ColumnValue = "30" });
            insertFieldList.Add(new ColumnWithValue { ColumnName = "TxnDate", ColumnValue = currentRow["TxnDate"] });
            insertFieldList.Add(new ColumnWithValue { ColumnName = "SupplierPk", ColumnValue = currentRow["SupplierPk"] });
            insertFieldList.Add(new ColumnWithValue { ColumnName = "TotalAmt", ColumnValue = currentRow["TotalAmt"] });
            insertFieldList.Add(new ColumnWithValue { ColumnName = "BTotalAmt", ColumnValue = currentRow["BTotalAmt"] });
            insertFieldList.Add(new ColumnWithValue { ColumnName = "GlPost", ColumnValue = "Y" });
            insertFieldList.Add(new ColumnWithValue { ColumnName = "TaxCal", ColumnValue = "N" });
            insertFieldList.Add(new ColumnWithValue { ColumnName = "AddUser", ColumnValue = GlobalVariables.LoggedInUser });
            insertFieldList.Add(new ColumnWithValue { ColumnName = "AddDate", ColumnValue = DateTime.Now });
            insertFieldList.Add(new ColumnWithValue { ColumnName = "AddMach", ColumnValue = Environment.MachineName + "#" + Environment.UserName });
            insertFieldList.Add(new ColumnWithValue { ColumnName = "CurPk", ColumnValue = currentRow["CurPk"] });
            insertFieldList.Add(new ColumnWithValue { ColumnName = "CurRate", ColumnValue = currentRow["CurRate"] });
            insertFieldList.Add(new ColumnWithValue { ColumnName = "GnLsCal", ColumnValue = "N" });
            insertFieldList.Add(new ColumnWithValue { ColumnName = "TotBal", ColumnValue = 0.00 });
            insertFieldList.Add(new ColumnWithValue { ColumnName = "glBatch", ColumnValue = "" });
            insertFieldList.Add(new ColumnWithValue { ColumnName = "Remarks", ColumnValue = "" });
            insertFieldList.Add(new ColumnWithValue { ColumnName = "PrtCopy", ColumnValue = 0 });
            
            var insertQuery = new Query { TableName = "fCdnHed", QType = QueryType.INSERT, ColValues = insertFieldList.ToArray() };
            var grnrService = DatabaseAccess.Instance;
            if (grnrService.Excecute(insertQuery, ref sqlTran) == null)
            {
                return false;
            }

                   //dtChildDet =dtChildDet.AsEnumerable()
                   //           .GroupBy(r => new {Col1 = r["RefNo"], Col2 = r["TxnDate"],Col3 = r["CurRate"]})
                   //        .Select(g => g.OrderBy(r => r["RefNo"]));
            return true;
        }
    }
}
